How to Convert Timestamp to Time in Excel

To convert a timestamp like "23.11.28.845000000" into a time format in Excel, start by breaking it into hours, minutes, seconds, and milliseconds. Assume 23 is hours, 11 is minutes, 28 is seconds, and 845000000 is nanoseconds. If the timestamp is in cell A1, use =LEFT(A1, 2) for hours, =MID(A1, 4, 2) for minutes, and =MID(A1, 7, 2) for seconds. Combine them using =TIME(...) and add milliseconds by dividing 845000000 by 10^9, then /86400 to convert to Excel’s day format. Format the cell as [h]:mm:ss.000 for precise display.

To convert a timestamp like "23.11.28.845000000" into a time in Excel, follow these steps:

=TIME(LEFT(A1, 2), MID(A1, 4, 2), MID(A1, 7, 2)) + VALUE(RIGHT(A1, LEN(A1) - 8)) / 10^9 / 86400

1. Split the Components:
   • Assume the format "HH.MM.SS.MMMMMMM," where:
     • HH is the hour,
     • MM is the minute,
     • SS is the second,
     • MMMMMMM represents milliseconds or fractions of a second.
2. Extract Components in Excel:
   • If the timestamp is in cell A1, use these formulas in separate cells to break down each part:
     • Hours: =LEFT(A1, 2)
     • Minutes: =MID(A1, 4, 2)
     • Seconds: =MID(A1, 7, 2)
     • Milliseconds: =RIGHT(A1, LEN(A1) - 8)
3. Convert to Excel Time:
   • Combine these components into an Excel-recognized time format using this formula:
   • TIME(...) creates the hour, minute, and second.
   • VALUE(...) / 10^9 / 86400 converts the milliseconds to Excel’s fractional day format.
     • 10^9 divides by nanoseconds.
     • 86400 converts seconds to Excel's day format.
4. Format the Result as Time:
   • Format the cell as [h]:mm:ss.000 for hours, minutes, seconds, and milliseconds display.

This should convert the timestamp into a readable time format in Excel.

To convert the timestamp "23.11.28.845000000" to hh:mm:ss format, a specific format, in Excel, you can follow these steps:

=TIME(VALUE(LEFT(A1, 2)), VALUE(MID(A1, 4, 2)), VALUE(MID(A1, 7, 2)))

1. Extract Components:
   • If your timestamp (e.g., "23.11.28.845000000") is in cell A1, use formulas to separate hours, minutes, and seconds:
     • Hours: =LEFT(A1, 2)
     • Minutes: =MID(A1, 4, 2)
     • Seconds: =MID(A1, 7, 2)
2. Combine Components as Excel Time:
   • Use the TIME function to convert these components to a time value.
3. Format the Result as hh:mm
   • Select the cell with the formula, go to Format Cells (Ctrl+1) > Custom and set the format to hh:mm:ss.

This will convert your timestamp into the hh:mm:ss format, ignoring milliseconds.